package edu.colorado.karl.databases;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.jfree.data.time.Minute;
import org.jfree.data.time.TimeSeries;
import org.jfree.data.time.TimeSeriesCollection;

/**
 * Database containing trend information from the house's sensors.
 */
public class LongTermDatabase {
	//	Used for log4j logging
	private static final Log log = LogFactory.getLog(LongTermDatabase.class);
	
	private static final int T_CT_1 = 1;
	private static final int T_CT_2 = 2;
	private static final int T_CT_3 = 3;
	private static final int T_HT_1 = 10;
	private static final int T_HT_2 = 11;
	private static final int T_OA = 12;
	private static final int T_PV_1 = 14;
	private static final int T_PV_2 = 15;
	
	private static final int T_PV_3 = 16;
	
	/**
	 * Returns the room and outside air temperatures.
	 * @return a list of temperatures.
	 */
	public ArrayList<SensorResponse> getAirTemperatures() {
		// List of responses
		ArrayList<SensorResponse> l = new ArrayList<SensorResponse>();
		// Database connection
		Connection c = LongTermDatabaseConnector.getConnection();
		
		if(c == null) {
			return l;	// Return empty list, error already logged
		}
		
		//	Pull the values from the database
		try {
			Statement s = c.createStatement();
			s.executeQuery("select DATA_VALUE_ from TRENDDATA " +
					"where TID_ = " + T_OA + " AND " + 
					"RECORD_TYPE_ = 2 order by DATE_STAMP_ desc limit 1;");
			ResultSet rs = s.getResultSet();
			
			if(rs.next()) {
				l.add(new SensorResponse("OAT", rs.getDouble(1)));
			}
			
		} catch (SQLException e) {
			log.warn("LTDB: SQL Exception", e);
		}
		
		LongTermDatabaseConnector.closeConnection(c);
		
		return l;
	}
	
	/**
	 * Retrieves the solar panel temperatures from the database.  As the solar
	 * panels have multiple sensors, the average temperature for is computed 
	 * and returned.
	 * @return a list with a single temperature for the solar panels.
	 */
	public ArrayList<SensorResponse> getSolarPanelTemperature() {
		// List of responses
		ArrayList<SensorResponse> l = new ArrayList<SensorResponse>();
		// Database connection
		Connection c = LongTermDatabaseConnector.getConnection();
		// Panel temperature
		double solarTemp = 0.0;
		// Number of working panel sensors
		int sensorCount = 0;
		
		if(c == null) {
			return l;	// Return empty list, error already logged
		}
		
		//	Pull the values from the database
		try {
			//	Get Solar Panels Temperature from the average of the 
			//	Solar Panels sensors
			Statement s = c.createStatement();
			s.executeQuery("select DATA_VALUE_ from TRENDDATA " +
					" where TID_ = " + T_PV_1 + " AND " + 
					"RECORD_TYPE_ = 2 order by DATE_STAMP_ desc limit 1;");
			ResultSet rs = s.getResultSet();
			
			if(rs.next()) {
				solarTemp += rs.getDouble(1);
				sensorCount++;
			}

			s.executeQuery("select DATA_VALUE_ from TRENDDATA " +
					"where TID_ = " + T_PV_2 + " AND " + 
					"RECORD_TYPE_ = 2 order by DATE_STAMP_ desc limit 1;");
			rs = s.getResultSet();

			if(rs.next()) {
				solarTemp += rs.getDouble(1);
				sensorCount++;
			}

			s.executeQuery("select DATA_VALUE_ from TRENDDATA " +
					"where TID_ = " + T_PV_3 + " AND " + 
					"RECORD_TYPE_ = 2 order by DATE_STAMP_ desc limit 1;");
			rs = s.getResultSet();

			if(rs.next()) {
				solarTemp += rs.getDouble(1);
				sensorCount++;
			}
			
			l.add(new SensorResponse("PVT", solarTemp / sensorCount));
		} catch (SQLException e) {
			log.warn("LTDB: SQL Exception", e);
		}
		
		LongTermDatabaseConnector.closeConnection(c);
		
		return l;
	}
	
	/**
	 * Retrieves the hot and cold tank temperatures from the database.  As both
	 * tanks have multiple sensors, the average temperature for each tank is 
	 * computed and returned.
	 * @return a list of sensor values.
	 */
	public ArrayList<SensorResponse> getTankTemperatures() {
		// List of responses
		ArrayList<SensorResponse> l = new ArrayList<SensorResponse>();
		// Database connection
		Connection c = LongTermDatabaseConnector.getConnection();
		// Tank temperatures
		double cTankTemp = 0.0, hTankTemp = 0.0;
		// Number of working tank sensors
		int cTankCount = 0, hTankCount = 0;
		
		if(c == null) {
			return l;	// Return empty list, error already logged
		}
		
		//	Pull the values from the database
		try {
			//	Get Cold Tank Temperature from the average of the Cold Tank	sensors
			Statement s = c.createStatement();
			s.executeQuery("select DATA_VALUE_ from TRENDDATA " +
					"where TID_ = " + T_CT_1 + " AND " + 
					"RECORD_TYPE_ = 2 order by DATE_STAMP_ desc limit 1;");
			ResultSet rs = s.getResultSet();
			
			if(rs.next()) {
				cTankTemp += rs.getDouble(1);
				cTankCount++;
			}

			s.executeQuery("select DATA_VALUE_ from TRENDDATA " +
					"where TID_ = " + T_CT_2 + " AND " + 
					"RECORD_TYPE_ = 2 order by DATE_STAMP_ desc limit 1;");
			rs = s.getResultSet();

			if(rs.next()) {
				cTankTemp += rs.getDouble(1);
				cTankCount++;
			}

			s.executeQuery("select DATA_VALUE_ from TRENDDATA " +
					"where TID_ = " + T_CT_3 + " AND " + 
					"RECORD_TYPE_ = 2 order by DATE_STAMP_ desc limit 1;");
			rs = s.getResultSet();

			if(rs.next()) {
				cTankTemp += rs.getDouble(1);
				cTankCount++;
			}
			
			l.add(new SensorResponse("CT", cTankTemp / cTankCount));
			
			//	Get Hot Tank Temperature from the average of the Hot Tank sensors
			s.executeQuery("select DATA_VALUE_ from TRENDDATA " +
					"where TID_ = " + T_HT_1 + " AND " + 
					"RECORD_TYPE_ = 2 order by DATE_STAMP_ desc limit 1;");
			rs = s.getResultSet();

			if(rs.next()) {
				hTankTemp += rs.getDouble(1);
				hTankCount++;
			}

			s.executeQuery("select DATA_VALUE_ from TRENDDATA " +
					"where TID_ = " + T_HT_2 + " AND " + 
					"RECORD_TYPE_ = 2 order by DATE_STAMP_ desc limit 1;");
			rs = s.getResultSet();

			if(rs.next()) {
				hTankTemp += rs.getDouble(1);
				hTankCount++;
			}
			
			l.add(new SensorResponse("HT", hTankTemp / hTankCount));
			
		} catch (SQLException e) {
			log.warn("LTDB: SQL Exception", e);
		}
		
		LongTermDatabaseConnector.closeConnection(c);
		
		return l;
	}
	
	/**
	 * Returns the values of the specified sensors for the specified time range.
	 * @param start			A date denoting the start time for the range
	 * @param end			A date denoting the end time for the range
	 * @param sensorNames	An array of sensor names indicating which sensors
	 * to use in the format: "TID#,SensorName"
	 * @return a collection of values containing the sensors' data during the
	 * specified range.
	 */
	public TimeSeriesCollection getValuesForTimeRange(Date start, Date end, 
													  String[] sensorNames) {
		Connection c = LongTermDatabaseConnector.getConnection();
		TimeSeriesCollection tsc = new TimeSeriesCollection();
				
		try{
			for(int i = 0; i < sensorNames.length; i++) {
				Statement s = c.createStatement();
				Timestamp tSStart = new Timestamp(start.getTime());
				Timestamp tSEnd = new Timestamp(end.getTime());
				s.executeQuery ("SELECT DATE_STAMP_, DATA_VALUE_ FROM TRENDDATA " +
						"WHERE TID_ = " + sensorNames[i].substring(0, sensorNames[i].indexOf(","))
						+ " AND DATE_STAMP_ > \"" + tSStart.toString() +
						"\" AND DATE_STAMP_ < \"" + tSEnd.toString() + "\"");
				ResultSet rs = s.getResultSet();
				//ResultSetMetaData meta = rs.getMetaData();
				TimeSeries mySeries = new TimeSeries(sensorNames[i].substring(
						sensorNames[i].indexOf(",")+1) + " Data", Minute.class);
			
				while(rs.next()) {
					Date d = new Date(rs.getTimestamp(1).getTime());
					mySeries.addOrUpdate(new Minute(d), rs.getDouble(2));
				}
				tsc.addSeries(mySeries);
			}
		}
		catch (SQLException e) { 
			log.debug("SQL Exception");
		}
		
		LongTermDatabaseConnector.closeConnection(c);
		return tsc;
	}
}
